Microsoft Excel has several different of these text based functions left right mid and just a few of
them.
We've got length or Lin function where.
Take a look at another one here called search.
Now I do want to point out that there's actually two functions that are very similar to one another
we're going to take a look at serged But there's another one called find.
And as another homework assignment for you I recommend taking a look at the difference between these
two functions.
All right.
Now here's the search function and here's the setup Here's the example open in front of you.
I've got the exercise File once again that comes with this course and I've got the tab called Search
function active in front of you here I've got a simple little lists a few names and two empty columns
.
Now in the Name column we've got two values.
We get first name and last name.
I'm sure that if you haven't already you will at some point run into a list that's formatted such as
this.
Two values like first name last name combined into one cell.
Maybe it's not name maybe its address or some some other value where I really want to break them up
into two separate columns.
Right now there's multiple different ways to do this.
Some of you maybe use text to columns to accomplish this text the columns is great if you haven't used
it it's on your data tab.
You can site a group of data and based on a delimiter something that separates the values you can break
them up into their individual values.
In this case we've got a space every single name here has a space that separates the two values first
name and last name.
So text the cons could be a good candidate in this situation but it's not always effective.
So we're going to take a look a year is utilizing the search function and perhaps some other functions
to help us break this up make this a little more robust so can handle just about any situation you throw
at it.
Take a look.
So I'm going to jump into the first name call.
I want to know the first name of each of these names right.
Patrick Joe Brint Joe.
MARTIN I want to break this up.
Well earlier if you took part of this course we talked about left right mid.
Well could I use one of those functions to extract the first name out of the A-Kon.
Well sure.
Like you do this like you jump into a beat to the beat to like say equals left because the first names
are on the left hand side.
Open up a print CS wants to know two things right.
Wants to know where the text is at.
Well that's easy that's an eight to comma and it wants to know how many characters.
Well let's Patrick guy here's got one two three four five six seven characters.
I'll type in seven.
Close my parentheses then my anarchy.
There go there's Patrick.
Now if I copy this down to.
Oh.
What's wrong.
I'm getting Joe foe and Brint B and Joe Pass and Martin.
Now I'm getting getting perhaps too much information here.
Martin looks pretty good but the rest of them I got pieces are their last names.
Well the function that I created here using left had a hard number.
It was always going to grab 7 so I could do this right.
I could go to each individual function and update it say Well no I only need three here not seven and
do that manually.
But that's quite the task.
Let's fix this.
So insteps a function called search the search function will allow us to search a string a collection
of values like Patrick Marleau.
It allows to search that for a specific character bolt.
What do you think I'm going to search for them I guess search for a or.
And what am I trying to do here.
I'm trying to get just the first name right.
So when I search for the space I need to know where the space is X so it's trines once again and then
jump back into BE2 to make this a little bit easier.
I'll go back to formulas text and I'm going to find I'm not going to start with search.
I want the whole purpose years I want to extract the first name the first names are where positionally
Where is first name at it's on the left hand side so I'm going to use left.
All right.
First they want to know hey well where's the text at too simple.
Well next parts not so simple we need know a dynamic number of characters right.
I can't just put 7 in there.
We saw that problem earlier.
Well here we're going to find the space using the search function and really what the search function
does.
It returns the position of that character we're searching for.
Watch this some is a search open up a print ceased and once again make this easier.
I mean look up into my formula bar into the search function.
Here we go search has three things.
Find what text.
Where am I going to find it.
And is there a specific character that you want me to start at.
What number position you want me start with.
We're not going to use that but I'm going to tell it to find the space in the way that I do this is
by doing quotes space coopt literally whatever we put in the quotes is literally what Excel will search
for.
In this case we're searching for space.
So just two quotes with the space in between.
Now where are you going to find it.
Well it's going to fine instead of to like this.
Where searching first base inside of A2 which is Patrick Marleau and it says hey it's at the eighth
position.
P A T R I C K that 7 and 8 is a space.
Look at it found it.
So now go back to left me a quick left up here at the top.
So we've told it a I want you to look at cell 2 and I want you to give me this number of characters
.
Dynamic range of characters right.
Find the eight offhand OK there's Patrick and I'll copy it down.
Joe Brint Joe and Martin a dynamic number here.
Why not just drop in in the seven or three Haftung update formulas manually.
But we're using nesting.
We're taking the search function nesting into the Left function and able to extract the first names
.
Now let's try this once again this is then taken a step further.
I want to get last name.
Well this one's going to be a bit more involved.
Let's think about this for a moment.
I need to get the last names were the last names at positionally where they at.
They're on the right.
Marlo Thorton Birns Pavelski.
Jones right there on the right hand side of that cell.
While we know we have a right function right.
Like to say this like say equal right.
Print CS wants to know two things.
Once tell where the text is at our minds and of ETU comma Canalis know how many characters and it's
different all over again right.
We got Marlowe which is one two three four five six seven character characters we got Thor and which
has eight characters burns with just five characters and so on.
So we're stuck in that same situation once again.
Now can I use a search for this.
What if I bring in search.
Now I'm going to open up my function window and hit the low eFax button here and see if this will help
me out at all.
Let's see.
Returns the number of characters out of specific at which specific character or string is first found
reading left to right.
And it's not case sensitive reading left to right.
What were our names at.
Our names are on the right hand side.
I'm not trying to read left to right.
I'm trying to read the other direction to get the last name.
So using the search all by itself isn't really going to help me here.
I mean to cancel out.
All right we're going to bring in one more function here.
Hey we're going to get a little bit messy.
There's a little bit more involved here but we're creating this very dynamic very robust function that's
going to do all this work for us.
Let's do this.
So I'm going to see C-2.
Now remember I want to extract from the right hand side.
So I'm going to go into texts and I made a grab my right function.
All right.
Where's my text that was inside of a 2.
There it is number characters.
Now here's where it gets tricky and we get to do a little bit of math.
We talked about earlier the Lin function the length function which returns the number of characters
within a cell.
I'm going to bring the Lynn in here.
I'll say Linda Eliane and I made a grab a to close the princes.
So now I'm just using the layout function to tell me how many characters are in that cell.
Oh well there's 50 Patrick Marleau with the space or 15 characters.
OK great.
Well you know what I only need Marlo which is one two three four five six seven characters.
Well but then Thorntons eight characters and Burns is five characters and so on.
Right.
So I know total number of characters but now I need to know the difference right between the total number
and the actual count of last name.
Well how can we find that we'll use the search function and I mean to do some subtraction here.
I mean to say take that link which is a total of 15 or whatever and subtracts the search find that space
again.
So I mean to make this easier McQuiggan search up here.
And we're going to find the empty space so quote space quote We're going to find it within this text
.
Patrick Marlowe and I'm going to kick back into the right function.
All right.
I'm feeling good about this.
We got eight too.
We've got number characters which is going to be the total length 15 minus how many characters have
found that space sexually from on.
We're going to subtract that off and it tells me we've got seven.
Is that right.
M A R L E A U.
One two three four five six seven seven.
OK.
And there's Marlowe.
Let's try to Sanomat dragged down Thornton Burns Pavelski and Jones.
So by doing a little bit of Neston we're using the right function.
But in order to get a dynamic number back Wolf first find out how many characters are in there.
Use the lint function and then we'll subtract the previous characters up to the space whatever that
is whether it's eight characters or four characters or six characters whatever it is.
But by Nestea we're creating this much more dynamic much more robust much more real usable function
.
Remember don't use manually get Excel to do it for you.
So try this out first get the first name used that let function use the search function to find that
dynamic number.
Once you get a grasp on that try out the last name you know feel for working with these left right functions
.
But then bringing in these other functions nesting them in to make it more dynamic